package com.liuyucheng.idea_springboot.util;

import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

public class ExcelRowToColUtil {

    private static Workbook workbook;

    @Data
    @Accessors(chain = true)
    private static class Item{
        private String year;
        private String amount;
    }

    public static void main(String[] args) throws IOException {
        List<ExcelData> excelData = readExcel("C:\\Users\\刘宇诚\\Desktop\\2023至2025年5月，各年，各国别承保金额.xlsx");

        LinkedHashMap<String,List<Item>> map = new LinkedHashMap<>();
        for (ExcelData data : excelData) {
//            System.out.println(data);
            Item item = new Item().setYear(data.getYear()).setAmount(data.getAmount());
            if(map.get(data.getCountry())==null){
                List<Item> items = new ArrayList<>();
                items.add(item);
                map.put(data.getCountry(),items);
            }else{
                map.get(data.getCountry()).add(item);
            }
        }

        Sheet sheet = workbook.createSheet();
        Row row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("");
        row1.createCell(1).setCellValue("2023年");
        row1.createCell(2).setCellValue("2024年");
        row1.createCell(3).setCellValue("2025年");


        int rowNum = 1;
        for(String key : map.keySet()){
            List<Item> items = map.get(key);
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(key);
            for (Item item : items) {
                String year = item.getYear().trim();
                String amount = item.getAmount();

                if(year.equals("2023年")){
                    row.createCell(1).setCellValue(amount);
                }else if(year.equals("2024年")){
                    row.createCell(2).setCellValue(amount);
                }else if(year.equals("2025年")){
                    row.createCell(3).setCellValue(amount);
                }
            }
        }

        // 保存 Workbook 到文件
        FileOutputStream outputStream = new FileOutputStream(new File("C:\\Users\\刘宇诚\\Desktop\\2023至2025年5月，各年，各国别承保金额-转换.xlsx"));
        workbook.write(outputStream);

        // 关闭资源
        outputStream.close();

    }

    public static List<ExcelData> readExcel(String filePath) throws IOException {
        List<ExcelData> dataList = new ArrayList<>();
        FileInputStream file = new FileInputStream(new File(filePath));
        workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        // 跳过标题行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            String year = row.getCell(0).getStringCellValue();
            String country = row.getCell(1).getStringCellValue();
            Number amount = row.getCell(2) == null ? null : row.getCell(2).getNumericCellValue();

            String amt;
            if(amount!=null){
                amt = new BigDecimal(amount.toString()).toPlainString();
            }else{
                amt = null;
            }

            ExcelData data = new ExcelData(year, country, amt);
            dataList.add(data);
        }

        file.close();
        return dataList;
    }


}
